-- @owner: lvlintao666
-- @date: 2024/05/13
-- @testpoint: 在where中使用prior递归查询
--step1:创建间隔分区表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_prior_0003;
create table t_prior_0003
(pid int,
id int,
name varchar(100),
"int1" tinyint,
"int2" smallint,
"int4" integer,
"int8" bigint,
"float4" float4,
"float8" float8,
"numeric" decimal(20, 6),
"bit4" bit(4),
"boolean" boolean,
"date" date,
"time" time,
"timetz" timetz,
"timestamp" timestamp,
"timestamptz" timestamptz,
"char" char(100),
"varchar" varchar(100),
"blob" blob,
"text" text
);

insert into t_prior_0003 values (0,1,'node1',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0003 values (1,2,'node2',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0003 values (1,3,'node3',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0003 values (2,4,'node4',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0003 values (2,5,'node5',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0003 values (3,6,'node6',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0003 values (3,7,'node7',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');

--step2 在where中使用prior查询出正确结果;expect:成功
select prior name as father_name, prior "int1" as father_int1, name as current_name, "int1" as current_int1 from t_prior_0003  where prior "int1" = 1 start with id = 1 connect by prior id = pid order by name ASC;

--step3 清理环境;expect:成功
drop table if exists t_prior_0003 cascade;